【アップデート情報】BigQueryでJson functionsが新たに追加されました

hanzawa.yuya

2023.08.11

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Google Cloudデータエンジニアのはんざわです。
2023年8月7日のアップデートでいくつかのJson functionsが新たにGAになったので紹介します。

release-notes August_07_2023

新たに追加されたJson functions

今回のアップデートで合計で11個のJson functionsが新たにGAになりました。

JSON_ARRAY：JSON_ARRAYの公式ドキュメント
JSON_ARRAY_APPEND：JSON_ARRAY_APPENDの公式ドキュメント
JSON_ARRAY_INSERT：JSON_ARRAY_INSERTの公式ドキュメント
JSON_OBJECT：JSON_OBJECTの公式ドキュメント
JSON_REMOVE：JSON_REMOVEの公式ドキュメント
JSON_SET：JSON_SETの公式ドキュメント
JSON_STRIP_NULLS：JSON_STRIP_NULLSの公式ドキュメント
LAX_BOOL：LAX_BOOLの公式ドキュメント
LAX_FLOAT64：LAX_FLOAT64の公式ドキュメント
LAX_INT64：LAX_INT64の公式ドキュメント
LAX_STRING：LAX_STRINGの公式ドキュメント

それぞれの関数の詳細は上記の公式ドキュメントを参照してみてください。
例も豊富で非常に見やすかったです。

また、公式のブログもあるので併せて参考にしてみてください。

今回の記事ではこの中でも自分が特に注目しているJSON_OBJECTとJSON_REMOVEを紹介したいと思います。

JSON_OBJECT

JSON_OBJECTではKeyとValueを渡すことでJsonオブジェクトを作成することができます。
以下の例では、fooに10を渡し、barにTRUEを渡しています。

SELECT JSON_OBJECT('foo', 10, 'bar', TRUE) AS json_data

/*-----------------------*
 | json_data             |
 +-----------------------+
 | {"bar":true,"foo":10} |
 *-----------------------*/

これの何が嬉しいかというと既存のデータ基盤を丸ごとJsonオブジェクトに変換し、Json型で保持することが可能になりました。

実際に触ってみる

今回の検証ではサンプルデータセットのtrigramsを使用します。

下記の画像はJson型変換前のテーブル情報です。

次に以下のクエリで対象のテーブルをJson型で丸ごと保持するようにします。

CREATE OR REPLACE TABLE test.json_object_table AS
SELECT
    JSON_OBJECT
    (
      'ngram', ngram,
      'first', first,
      'second', second,
      'third', third,
      'fourth', fourth,
      'fifth', fifth,
      'cell', cell
    ) AS json_data 
FROM
    samples_dataset.trigrams

正常にjson_dataカラムのJson型として丸ごと保持することができました。
次にテーブル情報を確認してみましょう。

通常のテーブルと比べて、Json型のテーブルは物理バイト数が減っていることがわかると思います。
BigQueryの新料金プランでは、物理ストレージを基準に課金されるため物理ストレージ量を減らすことがコスト削減に繋がると思います。

JSON_REMOVE

JSON_REMOVEはJsonデータから指定したパスやキーのデータを削除することができます。

Json型のARRAY

SELECT JSON_REMOVE(JSON '["a", "b", "c"]', '$[1]') AS json_data

/*-----------*
 | json_data |
 +-----------+
 | ["a","c"] |
 *-----------*/

/* 削除対象を複数選択することも可能 */
SELECT JSON_REMOVE(JSON '["a", "b", "c"]', '$[0]', '$[0]') AS json_data

/*-----------*
 | json_data |
 +-----------+
 | ["c"]     |
 *-----------*/

Jsonオブジェクト

SELECT JSON_REMOVE(JSON '{"a": 1, "b": {"c": "2"}}', '$.a') AS json_data

/*-----------------*
 | json_data       |
 +-----------------+
 | {"b":{"c":"2"}} |
 *-----------------*/

/* 階層化されたJsonもアクセス可能 */
SELECT JSON_REMOVE(JSON '{"a": 1, "b": {"c": "2"}}', '$.b.c') AS json_data

/*----------------*
 | json_data      |
 +----------------+
 | {"a":1,"b":{}} |
 *----------------*/

弊チームでは既にUDFでJson型から要素を削除する関数を作成し、使用していたのでそれが正式にサポートされるようになった感じです。

JSON_OBJECTの項目で紹介したように要素を丸ごとJson型データで保持するような場合、どうしても重複確認や結合キーの兼ね合いでそれに該当するカラムは事前に摘出する必要があります。
その際に摘出したカラムをJson型データから削除するとさらにストレージ効率が高くなると思います。
また、特定のカラムに機密情報などが含まれる場合も削除することが望ましいと思われます。